package com.csoa.community.db;

import java.util.ArrayList;
import java.util.List;

import android.database.Cursor;
import android.util.Log;

import com.csoa.community.bean.LocalSpeciesBean;
import com.csoa.community.utils.StringUtils;

public class LocalSpeciesInfoDB extends DataBaseDB {

	/**
	 * 样方信息插入操作
	 * 
	 * @param lm
	 */
	// 总共31项
	public static void insert(LocalSpeciesBean species) {
		DataBaseDB.getInst().getDB();
		String sql = "insert into T_Species_PlantCodeDic(" + "Id," + "Type ," + // 课题组id
				"SName  ," + // 所属调查方法
				"SpNumber  ," + // 中文名
				"Family  ," + // 拉丁名称
				"Genus  ," + // 俗名
				"Sp1  ," + // 性状
				"Author1  ," + // 数量
				"fullname  ," + // 盖度
				"Chinese  ," + // 高度
				"occurrence  ," + // 高度
				"originarea  ," + // 胸径
				"cultivated  ," + // 物候
				"endemism  ," + // 物候
				"Country  ," + // 生活力
				"Disprovin  " + // 生活力
				") values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		db.beginTransaction();
		try {
			db.execSQL(sql, new String[] { String.valueOf(species.Id),
					species.Type, species.SName, species.SpNumber,
					species.Family, species.Genus, species.Sp1,
					species.Author1, species.fullname, species.Chinese,
					species.occurrence, species.originarea, species.cultivated,
					String.valueOf(species.endemism), species.Country,
					species.Disprovin });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}

	public static void insert(String sql) {
		DataBaseDB.getInst().getDB();
		db.beginTransaction();
		try {
			Log.e("sql", sql);
			if(!StringUtils.isEmpty(sql)) {
			
				String[] insertSqls = sql.split(";");
				for(int i=0;i<insertSqls.length;i++) {
					db.execSQL(insertSqls[i]);
				}
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
	}

	/**
	 * 查询某个样方信息
	 * 
	 * @param lm
	 */
	public static LocalSpeciesBean queryById(String id) {
		DataBaseDB.getInst().getDB();
		String sql = "select " + "Id," + "Type ," + // 课题组id
				"SName  ," + // 所属调查方法
				"SpNumber  ," + // 中文名
				"Family  ," + // 拉丁名称
				"Genus  ," + // 俗名
				"Sp1  ," + // 性状
				"Author1  ," + // 数量
				"fullname  ," + // 盖度
				"Chinese  ," + // 高度
				"occurrence  ," + // 高度
				"originarea  ," + // 胸径
				"cultivated  ," + // 物候
				"endemism  ," + // 物候
				"Country  ," + // 生活力
				"Disprovin  " + // 生活力
				" from T_Species_PlantCodeDic where Id=?";
		db.beginTransaction();
		Cursor cursor = null;
		LocalSpeciesBean speciesInfo = null;
		try {
			cursor = db.rawQuery(sql, new String[] { id });
			if (cursor.getCount() > 0) {
				cursor.moveToNext();
				speciesInfo = parseWZInfo(cursor);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return speciesInfo;
	}

	/**
	 * 解析物种对象
	 * 
	 * @param wzInfo
	 * @param cursor
	 */
	private static LocalSpeciesBean parseWZInfo(Cursor cursor) {
		LocalSpeciesBean speciesInfo = new LocalSpeciesBean();
		speciesInfo.Id = cursor.getString(cursor.getColumnIndex("Id"));
		speciesInfo.Type = cursor.getString(cursor.getColumnIndex("Type"));
		speciesInfo.SName = cursor.getString(cursor.getColumnIndex("SName"));
		speciesInfo.SpNumber = cursor.getString(cursor
				.getColumnIndex("SpNumber"));
		speciesInfo.Family = cursor.getString(cursor.getColumnIndex("Family"));
		speciesInfo.Genus = cursor.getString(cursor.getColumnIndex("Genus"));
		speciesInfo.Sp1 = cursor.getString(cursor.getColumnIndex("Sp1"));
		speciesInfo.Author1 = cursor
				.getString(cursor.getColumnIndex("Author1"));
		speciesInfo.fullname = cursor.getString(cursor
				.getColumnIndex("fullname"));
		speciesInfo.Chinese = cursor
				.getString(cursor.getColumnIndex("Chinese"));
		speciesInfo.occurrence = cursor.getString(cursor
				.getColumnIndex("occurrence"));
		speciesInfo.originarea = cursor.getString(cursor
				.getColumnIndex("originarea"));
		speciesInfo.cultivated = cursor.getString(cursor
				.getColumnIndex("cultivated"));
		speciesInfo.endemism = cursor.getString(cursor
				.getColumnIndex("endemism"));
		speciesInfo.Country = cursor
				.getString(cursor.getColumnIndex("Country"));
		speciesInfo.Disprovin = cursor.getString(cursor
				.getColumnIndex("Disprovin"));
		return speciesInfo;
	}

	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<LocalSpeciesBean> query() {
		DataBaseDB.getInst().getDB();
		String sql = "select " + "Id," + "Type ," + // 课题组id
				"SName  ," + // 所属调查方法
				"SpNumber  ," + // 中文名
				"Family  ," + // 拉丁名称
				"Genus  ," + // 俗名
				"Sp1  ," + // 性状
				"Author1  ," + // 数量
				"fullname  ," + // 盖度
				"Chinese  ," + // 高度
				"occurrence  ," + // 高度
				"originarea  ," + // 胸径
				"cultivated  ," + // 物候
				"endemism  ," + // 物候
				"Country  ," + // 生活力
				"Disprovin  " + // 生活力
				" from T_Species_PlantCodeDic order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<LocalSpeciesBean> wzInfos = null;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor.getCount() > 0) {
				wzInfos = new ArrayList<LocalSpeciesBean>();
				while (cursor.moveToNext()) {
					LocalSpeciesBean wzInfo = parseWZInfo(cursor);
					wzInfos.add(wzInfo);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return wzInfos;
	}

	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<LocalSpeciesBean> query(String latinName,
			String chineseName) {
		DataBaseDB.getInst().getDB();
		String sql = "select " + "Id," + "Type ,"
				+ // 课题组id
				"SName  ,"
				+ // 所属调查方法
				"SpNumber  ,"
				+ // 中文名
				"Family  ,"
				+ // 拉丁名称
				"Genus  ,"
				+ // 俗名
				"Sp1  ,"
				+ // 性状
				"Author1  ,"
				+ // 数量
				"fullname  ,"
				+ // 盖度
				"Chinese  ,"
				+ // 高度
				"occurrence  ,"
				+ // 高度
				"originarea  ,"
				+ // 胸径
				"cultivated  ,"
				+ // 物候
				"endemism  ,"
				+ // 物候
				"Country  ,"
				+ // 生活力
				"Disprovin  "
				+ // 生活力
				" from T_Species_PlantCodeDic where " + "SName like ? and "
				+ "Chinese like ? " + "order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<LocalSpeciesBean> wzInfos = null;
		try {
			cursor = db.rawQuery(sql, new String[] { "%" + latinName + "%",
					"%" + chineseName + "%" });
			if (cursor.getCount() > 0) {
				wzInfos = new ArrayList<LocalSpeciesBean>();
				while (cursor.moveToNext()) {
					LocalSpeciesBean wzInfo = parseWZInfo(cursor);
					wzInfos.add(wzInfo);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return wzInfos;
	}

	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<String> queryChineseName(String chineseName) {
		DataBaseDB.getInst().getDB();
		String sql = "select " + "Id," + "Type ,"
				+ // 课题组id
				"SName  ,"
				+ // 所属调查方法
				"SpNumber  ,"
				+ // 中文名
				"Family  ,"
				+ // 拉丁名称
				"Genus  ,"
				+ // 俗名
				"Sp1  ,"
				+ // 性状
				"Author1  ,"
				+ // 数量
				"fullname  ,"
				+ // 盖度
				"Chinese  ,"
				+ // 高度
				"occurrence  ,"
				+ // 高度
				"originarea  ,"
				+ // 胸径
				"cultivated  ,"
				+ // 物候
				"endemism  ,"
				+ // 物候
				"Country  ,"
				+ // 生活力
				"Disprovin  "
				+ // 生活力
				" from T_Species_PlantCodeDic where " + " Chinese like ?  "
				+ "order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<String> names = null;
		try {
			if(chineseName == null) {
				chineseName = "";
			}
			cursor = db.rawQuery(sql, new String[] { "%" + chineseName + "%" });
			if (cursor.getCount() > 0) {
				names = new ArrayList<String>();
				while (cursor.moveToNext()) {
					String name = "";
					name = cursor.getString(cursor.getColumnIndex("Chinese"));
					names.add(name);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return names;
	}
	
	
	/**
	 * 查询所有样方信息
	 * 
	 * @param lm
	 */
	public static List<String> queryLatinName(String latinName) {
		DataBaseDB.getInst().getDB();
		String sql = "select " + "Id," + "Type ,"
				+ // 课题组id
				"SName  ,"
				+ // 所属调查方法
				"SpNumber  ,"
				+ // 中文名
				"Family  ,"
				+ // 拉丁名称
				"Genus  ,"
				+ // 俗名
				"Sp1  ,"
				+ // 性状
				"Author1  ,"
				+ // 数量
				"fullname  ,"
				+ // 盖度
				"Chinese  ,"
				+ // 高度
				"occurrence  ,"
				+ // 高度
				"originarea  ,"
				+ // 胸径
				"cultivated  ,"
				+ // 物候
				"endemism  ,"
				+ // 物候
				"Country  ,"
				+ // 生活力
				"Disprovin  "
				+ // 生活力
				" from T_Species_PlantCodeDic where " + " SName like ? "
				+ "order by id desc";
		db.beginTransaction();
		Cursor cursor = null;
		List<String> names = null;
		try {
			cursor = db.rawQuery(sql, new String[] { "%" + latinName + "%" });
			if (cursor.getCount() > 0) {
				names = new ArrayList<String>();
				while (cursor.moveToNext()) {
					String name = "";
					name = cursor.getString(cursor.getColumnIndex("SName"));
					names.add(name);
				}

			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
				cursor = null;

			}
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}
		return names;
	}

	/**
	 * 删除某一个用户的登录信息
	 * 
	 * @param LoginBean
	 */
	public static void delete(LocalSpeciesBean wzInfo) {
		DataBaseDB.getInst().getDB();
		String del = "delete from T_Species_PlantCodeDic where Id=?";
		db.beginTransaction();
		try {
			db.execSQL(del, new String[] { String.valueOf(wzInfo.Id) });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}

	}

	public static void delete() {
		DataBaseDB.getInst().getDB();
		String del = "delete from T_Species_PlantCodeDic ";
		db.beginTransaction();
		try {
			db.execSQL(del, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (db.isOpen()) {
				db.setTransactionSuccessful();
				db.endTransaction();
				db.close();
			}
		}

	}

}
